package com.apes.fn.rpt.profit;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 修改汇总sql需要同时修改菜单sql
 * todo 修改时记得把条件sql统一划出新变量
 */
@Component
public class GrossProfitDao extends ApesDao {

    EntityManager entityManager;

    /**
     * 耗材领用SQL
     */
    public final String SQL_STRING_HCLY="A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
            "                            AND A.INVENTORY_MODE = 'IM_OWN' " +
            "                           AND A.SCENE_ID = 'HC000' ";

    @Autowired
    private void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    /***
     * sSQL包装层，用于规范SQL，方便用于拼接
     */

    private String contactSQL(String sSQL, JSONObject param) {

        String contactSQL = "SELECT TO_CHAR(X.CREATE_DATE, 'YYYY-MM-DD') STATISTICS_DATE, X.*  FROM (" + sSQL + ") X WHERE 1 = 1";

        contactSQL += param.getString("SQL");
        contactSQL += "      ORDER BY CREATE_DATE,STATISTICS_DATE ASC,PRODUCT_ID";
        return contactSQL;
    }

    /**
     * 分页包装
     *
     * @param sSQL
     * @return
     */
    private String contactPageSQL(String sSQL) {
        String contactSQL = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( ";
        contactSQL += sSQL;
        contactSQL += " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start ";
        return contactSQL;
    }

    /**
     * 取总行数
     *
     * @param sSQL
     * @param param
     * @return
     */
    private String countSQL(String sSQL, JSONObject param) {

        String contactSQL = "SELECT COUNT(1) COUNT FROM (" + sSQL + ") X WHERE 1 = 1";
        contactSQL += param.getString("SQL");
        return contactSQL;
    }

    /***
     * 查询销售 二级菜单
     */
    public JSONObject querySales(JSONObject param) {
        String sSQL = "SELECT A.TYPE ORIGIN_TYPE, ID, PRICE_TOTAL, DISCOUNT, PRODUCT_QTY, " +
                "       PRICE_UNIT, AMOUNT, PRODUCT_ID, PRODUCT_NAME, DEPT_ID, DEPT_NAME, " +
                "       COMPANY_ID, COMPANY_NAME, STATISTICS_DATE CREATE_DATE, " +
                "       SERVE_TYPE_NAME TYPE, CAR_LICENSE " +
                "  FROM (SELECT '零售' TYPE, A.ID, B.PRICE_TOTAL, B.DISCOUNT_AMOUNT DISCOUNT, " +
                "                B.PRODUCT_UOM_QTY PRODUCT_QTY, B.PRICE_UNIT, " +
                "                B.PRICE_TOTAL - B.DISCOUNT_AMOUNT AMOUNT, B.PRODUCT_ID, " +
                "                P.NAME PRODUCT_NAME, A.DEPT_ID, A.COMPANY_ID, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                  WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "                A.DATE_APPROVE STATISTICS_DATE, CAR_LICENSE, " +
                "                (SELECT F.NAME FROM MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                  WHERE P.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                    AND F.ID = G.SERVE_TYPE_ID AND ROWNUM = 1) SERVE_TYPE_NAME " +
                "           FROM RETAIL_ORDER A " +
                "           LEFT JOIN RETAIL_ORDER_ITEM B ON A.ID = B.SALE_ID " +
                "           LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "           LEFT JOIN FN_CUSTOMER_EXPAND D ON A.CUSTOMER_EXPAND_ID = D.ID " +
                "          WHERE A.CLAIM_TYPE IS NULL " +
                "         UNION ALL " +
                "         SELECT '凭证' TYPE, A.ID, B.PRICE_TOTAL, B.DISCOUNT, B.PRODUCT_QTY, " +
                "                B.PRICE_UNIT, B.PRICE_TOTAL - B.DISCOUNT AMOUNT, B.PRODUCT_ID, " +
                "                P.NAME PRODUCT_NAME, A.DEPT_ID, A.COMPANY_ID, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                  WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "                A.CREATE_DATE STATISTICS_DATE, CAR_LICENSE, " +
                "                (SELECT F.NAME FROM MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                  WHERE P.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                    AND F.ID = G.SERVE_TYPE_ID AND ROWNUM = 1) SERVE_TYPE_NAME " +
                "           FROM ACC_VOUCHER A " +
                "           LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID = B.VOUCHER_ID " +
                "           LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "           LEFT JOIN FN_CUSTOMER_EXPAND D ON A.CUSTOMER_EXPAND_ID = D.ID " +
                "          WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE'  " +
                "          and A.ORIGIN_BUSINESS_TYPE <> 'SaleReturn'  " +
                "          AND A.INVENTORY_MODE = 'IM_OWN'  " +
                "                            AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.COMPANY_ID) !=  " +
                "                                 (SELECT t.Party_Id  " +
                "                                  FROM MAS_PARTY_ROLE t  " +
                "                                  WHERE T.ID = A.PARTNER_ID))  " +
                "and ((a.origin_business_type <> 'RetailReturn' " +
                        "                                                         AND (a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                                "                                                              OR A.CLASSIFICATION IS NULL) " +
                                "                                                                 ) " +
                                "                                                          or (a.origin_business_type = 'RetailReturn' and a.classification in ('08', '09'))) " +
                "                            and NVL(a.CLASSIFICATION,' ') <> 'CROSS_IC' " +
                "         UNION ALL " +
                "         SELECT '耗材领用' TYPE, A.ID, B.PRICE_TOTAL, B.DISCOUNT, B.PRODUCT_QTY, " +
                "                B.PRICE_UNIT, B.PRICE_TOTAL - B.DISCOUNT AMOUNT, B.PRODUCT_ID, " +
                "                P.NAME PRODUCT_NAME, A.DEPT_ID, A.COMPANY_ID, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                  WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "                A.CREATE_DATE STATISTICS_DATE, CAR_LICENSE, " +
                "                (SELECT F.NAME FROM MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                  WHERE P.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                    AND F.ID = G.SERVE_TYPE_ID AND ROWNUM = 1) SERVE_TYPE_NAME " +
                "           FROM ACC_VOUCHER A " +
                "           LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID = B.VOUCHER_ID " +
                "           LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "           LEFT JOIN FN_CUSTOMER_EXPAND D ON A.CUSTOMER_EXPAND_ID = D.ID " +
                "          WHERE  " + SQL_STRING_HCLY +
                "         UNION ALL " +
                "         SELECT '分销' TYPE, A.ID, B.PRICE_TOTAL PRICE_TOTAL, B.DISCOUNT, " +
                "                B.PRODUCT_UOM_QTY PRODUCT_QTY, B.PRICE_UNIT, " +
                "                B.PRICE_TOTAL - B.DISCOUNT AMOUNT, B.PRODUCT_ID, " +
                "                P.NAME PRODUCT_NAME, A.DEPT_ID, A.COMPANY_ID, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                  WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "                A.DATE_APPROVE STATISTICS_DATE, CAR_LICENSE, " +
                "                (SELECT F.NAME FROM MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                  WHERE P.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                    AND F.ID = G.SERVE_TYPE_ID AND ROWNUM = 1) SERVE_TYPE_NAME " +
                "           FROM SALE_ORDER A " +
                "           LEFT JOIN SALE_ORDER_ITEM B ON A.ID = B.SALE_ID " +
                "           LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "           LEFT JOIN FN_CUSTOMER_EXPAND D ON A.CUSTOMER_EXPAND_ID = D.ID " +
                "           WHERE A.ORDER_TYPE NOT IN ('INNER_SALE','RETAIL_SALE') " +
                "         UNION ALL " +
                "         SELECT '分销退货' TYPE, A.ID, B.PRICE_TOTAL * -1 PRICE_TOTAL, " +
                "                B.DISCOUNT * -1 DISCOUNT, B.PRODUCT_UOM_QTY PRODUCT_QTY, " +
                "                B.PRICE_UNIT, (B.PRICE_TOTAL - B.DISCOUNT) * -1 AMOUNT, " +
                "                B.PRODUCT_ID, P.NAME PRODUCT_NAME, A.DEPT_ID, A.COMPANY_ID, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                  WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME, " +
                "                A.DATE_APPROVE STATISTICS_DATE, CAR_LICENSE, " +
                "                (SELECT F.NAME FROM MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                  WHERE P.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                    AND F.ID = G.SERVE_TYPE_ID AND ROWNUM = 1) SERVE_TYPE_NAME " +
                "           FROM SALE_RETURN A " +
                "           LEFT JOIN SALE_RETURN_ITEM B ON A.ID = B.SALE_RETURN_ID " +
                "           LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "           LEFT JOIN FN_CUSTOMER_EXPAND D ON A.CUSTOMER_EXPAND_ID = D.ID) A";
        JSONArray jsonArray = toJSONArray(find(contactPageSQL(contactSQL(sSQL, param)), param));
        Object count = ((JSONObject) toJSONArray(find(countSQL(sSQL, param), param)).get(0)).get("COUNT");
        List<String> sumList = Arrays.asList("PRODUCT_QTY", "DISCOUNT", "PRICE_TOTAL", "AMOUNT");
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("content", jsonArray);
        jsonObject.put("footer", toJSONArray(find(footerSQL(sumList,sSQL, param), param)));
        jsonObject.put("total", count);
        jsonObject.put("pages", count);
        return jsonObject;
    }

    /**
     * 总汇总数
     * @param sumList
     * @param sSQL
     * @param param
     * @return
     */
    private String footerSQL(List<String> sumList,String sSQL, JSONObject param) {
        StringBuffer sf = new StringBuffer();
        for (int i = 0; i < sumList.size(); i++) {
            sf.append("SUM("+sumList.get(i)+") "+sumList.get(i));
            if (i!=sumList.size()-1){
                sf.append(",");
            }
        }
        String footerSQL = "SELECT "+sf.toString()+" FROM (" + sSQL + ") X  where 1=1";
        footerSQL += param.getString("SQL");
        return footerSQL;
    }

    /***
     * 查询已记账成本
     */
    public JSONObject queryVoucher(JSONObject param) {
        String sSQL = "SELECT  C.*, " +
                "       (CASE " +
                "            WHEN AMOUNT = 0 THEN " +
                "                0 " +
                "            ELSE " +
                "                    AMOUNT - COST - PLATFORM_MANAGEMENT_FEE - " +
                "                    SUPPLY_CHAIN_MANAGEMENT_FEE / " +
                "                    PRICE_TOTAL " +
                "           END)                      GROSS_RATE, " +
                "       (AMOUNT - COST - PLATFORM_MANAGEMENT_FEE - " +
                "        SUPPLY_CHAIN_MANAGEMENT_FEE) GROSS_PROFIT " +
                "         FROM ( " +
                "                  SELECT  " +
                "                                         V.BOOK_DATE    CREATE_DATE, " +
                "                                         A.SOURCE, " +
                "                                         V.ORIGIN_BUSINESS_TYPE                       ORIGIN_BUSINESS_TYPE, " +
                "                                         V.SOURCE_BUSINESS_TYPE                       SOURCE_BUSINESS_TYPE, " +
                "                                         A.ID, " +
                "                                         A.ORIGIN, " +
                "                                         A.COMPANY_ID, " +
                "                                         V.DEPT_NAME       DEPT_NAME, " +
                "                                         V.COMPANY_NAME                                    COMPANY_NAME, " +
                "                                         B.PLATFORM_MANAGEMENT_FEE - B.PLATFORM_SERVICE_FEE_REDUCED - PLATFORM_INTERNAL_REDUCED  PLATFORM_MANAGEMENT_FEE, " +
                "                                         B.SUPPLY_CHAIN_MANAGEMENT_FEE," +
                "                                         B.PLATFORM_SERVICE_FEE_REDUCED, " +
                "                                         B.PLATFORM_INTERNAL_REDUCED, " +
                "                                         A.DEPT_ID, " +
                "                                         V.SERVE_TYPE_NAME                                          TYPE, " +
                "                                         (B.PRICE_TOTAL - B.DISCOUNT)                                 AMOUNT,B.PRODUCT_ID, " +
                "                                         V.CAR_LICENSE CAR_LICENSE, " +
                "                                         B.PRICE_TOTAL,B.DISCOUNT,B.PRODUCT_QTY,B.PRICE_UNIT, " +
                "                                         P.NAME  PRODUCT_NAME, " +
                "                                         (SELECT SUM(E.TAX_COST_PRICE * " +
                "                                                     E.PRODUCT_QTY) " +
                "                                          FROM ACC_VOUCHER_INVENTORY E " +
                "                                          WHERE E.VOUCHER_ITEM_ID = B.ID)                             COST " +
                "                                        FROM ACC_VOUCHER A " +
                "                                                        LEFT JOIN " +
                "                                                           ACC_VOUCHER_ITEM B on A.ID = B.VOUCHER_ID " +
                "                                                        LEFT JOIN ACC_VOUCHER_EXTENSION V ON B.ID=V.ID " +
                "                                                        LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                                  WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "                                    AND A.INVENTORY_MODE = 'IM_OWN' " +
                "                                    AND A.ORIGIN_BUSINESS_TYPE not in ('ConsumableUse','ProfitAndLoss','InventoryTransferAsset')   " +
                "                                    AND (A.BUSINESS_TYPE IN " +
                "                                         ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn') OR " +
                "                                         A.ORIGIN_BUSINESS_TYPE IN " +
                "                                         ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn')) " +
                "                            ";
        if (param.containsKey("cost_type")  ) {
            if (Objects.equals(param.get("cost_type"), "accessory")){
                sSQL += " AND P.PRODUCT_GROUP_ID = '10' ";
            }else {
                sSQL += "AND P.PRODUCT_GROUP_ID <> '10'";
            }
        }
                sSQL+= "              )C ";
        String contactSQL = contactSQL(sSQL, param);
        JSONArray jsonArray = toJSONArray(find(contactPageSQL(contactSQL), param));
        StringBuffer countSql = new StringBuffer();
        countSql.append("SELECT COUNT(1) COUNT FROM (").append(contactSQL).append("     ) ");
        Object count = ((JSONObject) toJSONArray(find(countSql.toString(), param)).get(0)).get("COUNT");
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("content", jsonArray);
        if (param.getDoubleValue("start")<1){
            List<String> sumList = Arrays.asList("PRODUCT_QTY", "DISCOUNT", "PRICE_TOTAL", "AMOUNT", "COST", "PLATFORM_MANAGEMENT_FEE", "PLATFORM_SERVICE_FEE_REDUCED",
                    "PLATFORM_INTERNAL_REDUCED", "SUPPLY_CHAIN_MANAGEMENT_FEE", "GROSS_PROFIT");
            jsonObject.put("footer", toJSONArray(find(footerSQL(sumList,sSQL, param), param)));
        }
        jsonObject.put("total", count);
        jsonObject.put("pages", count);
        return jsonObject;
    }

    /***
     * 资金收入单
     */
    public JSONArray queryMoneyIncome(JSONObject param) {
        String sSQL = "SELECT A.POST_DATE CREATE_DATE, '资金收入单' TYPE,  A.ID, B.INCOME_AMOUNT, B.PLATFORM_MANAGEMENT_FEE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, A.DEPT_ID, " +
                "       (SELECT E.NAME FROM MAS_AMT_RECEIPT E WHERE B.AMT_RECEIPT_ID = E.ID) AMT_RECEIPT_NAME, " +
                "       (SELECT E.NAME " +
                "           FROM MAS_PAYMENT_METHOD E " +
                "          WHERE B.PAYMENT_METHOD_TYPE = E.ID) PAYMENT_METHOD_TYPE_NAME, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE E.PARTY_ID = F.ID " +
                "            AND E.ID = A.COMPANY_ID) COMPANY_NAME,'' PRODUCT_ID " +
                "  FROM MONEY_INCOME A, MONEY_INCOME_ITEM B " +
                " WHERE A.ID = B.MONEY_INCOME";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 定损介绍费
     */
    public JSONArray querySettleExpense(JSONObject param) {
        String sSQL = "SELECT A.CREATE_DATE, A.CAR_LICENSE, A.CASE_TYPE, A.LOSS_QUOTATION_AMT, " +
                "       A.AMOUNT, A.SOURCE, A.ORIGIN, '定损介绍费' EXPENSE_TYPE, '定损介绍费' TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.SOURCE_BUSINESS_TYPE) SOURCE_BUSINESS_TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.ORIGIN_BUSINESS_TYPE) ORIGIN_BUSINESS_TYPE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID, " +
                "       (SELECT E.DESCRIPTION FROM MAS_ENUMERATION E WHERE A.CASE_TYPE = E.ID) CASE_TYPE_NAME, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE E.PARTY_ID = F.ID " +
                "            AND E.ID = A.PAYMENT_ID) PAYMENT_NAME, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE E.PARTY_ID = F.ID " +
                "            AND E.ID = A.TENANT_ID) TENANT_NAME, " +
                "       DECODE(A.TYPE, 'in', '收入', 'out', '支出', '') IN_OR_OUT,'' PRODUCT_ID " +
                "  FROM FN_SETTLE_EXPENSE A " +
                " WHERE A.EXPENSE_TYPE = 'infoExpense' " +
                "   AND A.DEPT_ID IS NOT NULL " +
                "UNION ALL " +
                "SELECT A.CREATE_DATE, A.CAR_LICENSE, A.CASE_TYPE, A.LOSS_QUOTATION_AMT, " +
                "       A.AMOUNT, A.SOURCE, A.ORIGIN, '平台管理费' EXPENSE_TYPE, '定损介绍费' TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.SOURCE_BUSINESS_TYPE) SOURCE_BUSINESS_TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.ORIGIN_BUSINESS_TYPE) ORIGIN_BUSINESS_TYPE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID, " +
                "       (SELECT E.DESCRIPTION FROM MAS_ENUMERATION E WHERE A.CASE_TYPE = E.ID) CASE_TYPE_NAME, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE E.PARTY_ID = F.ID " +
                "            AND E.ID = A.PAYMENT_ID) PAYMENT_NAME, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE E.PARTY_ID = F.ID " +
                "            AND E.ID = A.TENANT_ID) TENANT_NAME, " +
                "       DECODE(A.TYPE, 'in', '收入', 'out', '支出', '') IN_OR_OUT,'' PRODUCT_ID " +
                "  FROM FN_SETTLE_EXPENSE A " +
                " WHERE A.EXPENSE_TYPE = 'platformExpense' " +
                "   AND A.DEPT_ID IS NOT NULL" +
                " union all " +
                "    SELECT A.CREATE_DATE, " +
                "             A.CAR_LICENSE, " +
                "             A.CASE_TYPE, " +
                "             A.LOSS_QUOTATION_AMT, " +
                "             A.AMOUNT, " +
                "             A.SOURCE, " +
                "             A.ORIGIN, " +
                "             '定损管理费'                               EXPENSE_TYPE, " +
                "             '定损介绍费'                               TYPE, " +
                "             (SELECT E.NAME " +
                "              FROM SOE_BUSINESS_TYPE E " +
                "              WHERE E.ID = A.SOURCE_BUSINESS_TYPE) SOURCE_BUSINESS_TYPE, " +
                "             (SELECT E.NAME " +
                "              FROM SOE_BUSINESS_TYPE E " +
                "              WHERE E.ID = A.ORIGIN_BUSINESS_TYPE) ORIGIN_BUSINESS_TYPE, " +
                "             (SELECT E.NAME " +
                "              FROM MAS_DEPT E " +
                "              WHERE A.DEPT_ID = E.ID)              DEPT_NAME, " +
                "             A.DEPT_ID, " +
                "             (SELECT E.DESCRIPTION " +
                "              FROM MAS_ENUMERATION E " +
                "              WHERE A.CASE_TYPE = E.ID)            CASE_TYPE_NAME, " +
                "             (SELECT F.NAME " +
                "              FROM MAS_PARTY_ROLE E, " +
                "                   MAS_PARTY F " +
                "              WHERE E.PARTY_ID = F.ID " +
                "                AND E.ID = A.PAYMENT_ID)           PAYMENT_NAME, " +
                "             (SELECT F.NAME " +
                "              FROM MAS_PARTY_ROLE E, " +
                "                   MAS_PARTY F " +
                "              WHERE E.PARTY_ID = F.ID " +
                "                AND E.ID = A.TENANT_ID)            TENANT_NAME, " +
                "             DECODE(A.TYPE, " +
                "                    'in', '收入', 'out', '支出', '')   IN_OR_OUT, " +
                "             ''                                    PRODUCT_ID " +
                "      FROM FN_SETTLE_EXPENSE A " +
                "      WHERE A.EXPENSE_TYPE " +
                "          = 'lossQuotationExpense' " +
                "        AND A.DEPT_ID IS NOT NULL " +
                "            AND (SELECT D.COMPANY_ID " +
                "                         FROM MAS_DEPT d " +
                "                         WHERE D.ID = a.DEPT_ID) = a.PAYMENT_ID";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 车险佣金
     */
    public JSONArray queryPolicyInformation(JSONObject param) {
        String sSQL = "SELECT A.CREATE_DATE, A.PLATE_LICENSE, A.VIN, A.INSURANCE_AMOUNT, " +
                "       B.FN_COMMISSION, B.PLATFORM_MANAGEMENT_FEE, '管理服务费' TYPE,'' PRODUCT_ID, " +
                "       DECODE(A.INSURANCE_TYPE, '0', '商业险', '1', '交强险', '') INSURANCE_TYPE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_ATTR_VALUE E " +
                "          WHERE B.COMMISSION_TYPE = E.ID) COMMISSION_TYPE " +
                "  FROM POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B " +
                " WHERE A.ID = B.POLICY_INFORMATION_ID";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 二手车服务费
     */
    public JSONArray queryUsedCar(JSONObject param) {
        String sSQL = "SELECT A.DATE_APPROVE CREATE_DATE, A.STORE_PRICE, A.LICENSE_PLATE, A.FINAL_PRICE, " +
                "       A.PLATFORM_MANAGEMENT_FEE, '二手车服务费' TYPE， " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID,'' PRODUCT_ID " +
                "  FROM WAR_USED_CAR A " +
                " WHERE A.DATE_APPROVE IS NOT NULL";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 用户储值/定金
     * （零售单/定金单）
     */
    public JSONArray queryRetailOrderAndAdvancePayment(JSONObject param) {
        String sSQL = "SELECT A.DATE_APPROVE CREATE_DATE, A.ID, B.PRICE_TOTAL, B.PLATFORM_MANAGEMENT_FEE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE, " +
                "       (SELECT E.CAR_LICENSE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "       (SELECT " +
                "           (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME,B.PRODUCT_ID, " +
                "       A.DEPT_ID, '会员储值/订金' TYPE " +
                "  FROM RETAIL_ORDER A, RETAIL_ORDER_ITEM B " +
                " WHERE A.ID = B.SALE_ID " +
                "   AND EXISTS " +
                " (SELECT 1 " +
                "          FROM MAS_PRODUCT E, MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "         WHERE E.CLASSIFY_ID = G.CLASSIFY_ID " +
                "           AND F.ID = G.SERVE_TYPE_ID " +
                "           AND B.PRODUCT_ID = E.ID " +
                "           AND F.ID = '012') " +
                "UNION ALL " +
                "SELECT A.DATE_APPROVE CREATE_DATE, A.ID, B.PRICE_TOTAL, B.PLATFORM_MANAGEMENT_FEE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE, " +
                "       (SELECT E.CAR_LICENSE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "       (SELECT " +
                "           (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME,B.PRODUCT_ID, " +
                "       A.DEPT_ID, '会员储值/订金' TYPE " +
                "  FROM ADVANCE_PAYMENT A, ADVANCE_PAYMENT_ITEM B " +
                " WHERE A.ID = B.ADVANCE_PAYMENT_ID " /*+
                "   AND EXISTS " +
                " (SELECT 1 " +
                "          FROM MAS_PRODUCT E, MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "         WHERE E.CLASSIFY_ID = G.CLASSIFY_ID " +
                "           AND F.ID = G.SERVE_TYPE_ID " +
                "           AND B.PRODUCT_ID = E.ID " +
                "           AND F.ID = '012')"*/
                ;
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 年卡
     * （零售单）
     */
    public JSONArray queryRetailOrder(JSONObject param) {
        String sSQL = "SELECT A.DATE_APPROVE CREATE_DATE, A.ID, B.PRICE_TOTAL, " +
                "               B.PLATFORM_MANAGEMENT_FEE, " +
                "               (SELECT E.NAME " +
                "                   FROM SOE_BUSINESS_TYPE E " +
                "                  WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE, " +
                "               (SELECT E.CAR_LICENSE " +
                "                   FROM FN_CUSTOMER_EXPAND E " +
                "                  WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "               (SELECT " +
                "           (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE " +
                "                   FROM FN_CUSTOMER_EXPAND E " +
                "                  WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE, " +
                "               (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "               A.DEPT_ID, '购买年卡' TYPE,B.PRODUCT_ID " +
                "          FROM RETAIL_ORDER A, RETAIL_ORDER_ITEM B " +
                "         WHERE A.ID = B.SALE_ID " +
                "           AND EXISTS " +
                "         (SELECT 1 " +
                "                  FROM MAS_PRODUCT E, MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                 WHERE E.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                   AND F.ID = G.SERVE_TYPE_ID " +
                "                   AND B.PRODUCT_ID = E.ID " +
                "                   AND F.ID = '010')";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }


    /***
     * 会员储值/定金消费
     */
    public JSONArray queryRetailOrderCost_recharge(JSONObject param) {
        //2022年10月31日 16:00:14 会员储值消费 修改为 储值/订金消费
        String sSQL = "SELECT A.DATE_APPROVE CREATE_DATE, A.ID, -1 * B.AMOUNT AMOUNT, " +
                "       B.PLATFORM_MANAGEMENT_FEE, '储值/订金消费' TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE, " +
                "       (SELECT E.CAR_LICENSE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "       (SELECT " +
                "           (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE, " +
                "       (SELECT E.NAME " +
                "           FROM MAS_PAYMENT_METHOD E " +
                "          WHERE E.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID,'' PRODUCT_ID " +
                "  FROM RETAIL_ORDER A, RETAIL_ORDER_PAYMENT_METHOD B " +
                " WHERE A.ID = B.RETAIL_ORDER_ID " +
                "   AND B.PAYMENT_METHOD_TYPE IN ('05', '12')" +
                "   union all\n" +
                " SELECT A.DATE_APPROVE CREATE_DATE, A.ID, -1 * B.AMOUNT AMOUNT,\n" +
                "                  B.PLATFORM_MANAGEMENT_FEE, '储值/订金消费' TYPE,\n" +
                "                  (SELECT E.NAME\n" +
                "                      FROM SOE_BUSINESS_TYPE E\n" +
                "                     WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE,\n" +
                "                  (SELECT E.CAR_LICENSE\n" +
                "                      FROM FN_CUSTOMER_EXPAND E\n" +
                "                     WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE,\n" +
                "                  (SELECT\n" +
                "                      (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE\n" +
                "                      FROM FN_CUSTOMER_EXPAND E\n" +
                "                     WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE,\n" +
                "                  (SELECT E.NAME\n" +
                "                      FROM MAS_PAYMENT_METHOD E\n" +
                "                     WHERE E.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "                  (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME,\n" +
                "                  A.DEPT_ID,'' PRODUCT_ID\n" +
                "             FROM ADVANCE_PAYMENT A, ADVANCE_PAYMENT_METHOD B\n" +
                "            WHERE A.ID = B.PAYMENT_ID\n" +
                "              AND B.PAYMENT_METHOD_TYPE IN ('05', '12')";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 年卡消费
     */
    public JSONArray queryRetailOrderCost_yearCard(JSONObject param) {
        String sSQL = "SELECT A.DATE_APPROVE CREATE_DATE, A.ID, -1 * B.AMOUNT AMOUNT, B.PLATFORM_MANAGEMENT_FEE, '年卡消费' TYPE, " +
                "       (SELECT E.NAME " +
                "           FROM SOE_BUSINESS_TYPE E " +
                "          WHERE E.ID = A.INNER_BUSINESS_TYPE) INNER_BUSINESS_TYPE, " +
                "       (SELECT E.CAR_LICENSE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "       (SELECT " +
                "           (GET_ENCRYPT_MOBILE(E.MOBILE)) AS MOBILE " +
                "           FROM FN_CUSTOMER_EXPAND E " +
                "          WHERE A.CUSTOMER_EXPAND_ID = E.ID) MOBILE, " +
                "       (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "       A.DEPT_ID,'' PRODUCT_ID " +
                "  FROM RETAIL_ORDER A, RETAIL_ORDER_PAYMENT_METHOD B " +
                " WHERE A.ID = B.RETAIL_ORDER_ID " +
                "   AND PAYMENT_METHOD_TYPE = '10' AND B.AMOUNT <> 0";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }


    /****
     * 耗材领用
     */
    public JSONArray queryConsumableUse(JSONObject param) {
        String sSQL = "SELECT C.*, " +
                "       (C.PRICE_TOTAL - C.COST - C.PLATFORM_MANAGEMENT_FEE - " +
                "        C.SUPPLY_CHAIN_MANAGEMENT_FEE) PROFIT " +
                "  FROM (SELECT A.CREATE_DATE, A.SOURCE, A.ID, A.ORIGIN, " +
                "                B.PLATFORM_MANAGEMENT_FEE, B.SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                (SELECT E.NAME " +
                "                    FROM SOE_BUSINESS_TYPE E " +
                "                   WHERE E.ID = A.SOURCE_BUSINESS_TYPE) SOURCE_BUSINESS_TYPE, " +
                "                (SELECT E.NAME " +
                "                    FROM SOE_BUSINESS_TYPE E " +
                "                   WHERE E.ID = A.ORIGIN_BUSINESS_TYPE) ORIGIN_BUSINESS_TYPE, " +
                "                (SELECT E.NAME FROM MAS_DEPT E WHERE A.DEPT_ID = E.ID) DEPT_NAME, " +
                "                A.DEPT_ID, " +
                "                (SELECT E.CAR_LICENSE " +
                "                    FROM FN_CUSTOMER_EXPAND E " +
                "                   WHERE A.CUSTOMER_EXPAND_ID = E.ID) CAR_LICENSE, " +
                "                (SELECT E.NAME FROM MAS_PRODUCT E WHERE B.PRODUCT_ID = E.ID) PRODUCT_NAME, " +
                "                B.PRODUCT_ID, B.PRODUCT_QTY, B.PRODUCT_UOM_ID, " +
                "                (SELECT E.NAME " +
                "                    FROM MAS_PRODUCT_UOM E " +
                "                   WHERE B.PRODUCT_UOM_ID = E.ID) PRODUCT_UOM_NAME, " +
                "                (SELECT F.NAME " +
                "                    FROM MAS_PRODUCT E, MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                   WHERE E.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                     AND F.ID = G.SERVE_TYPE_ID " +
                "                     AND B.PRODUCT_ID = E.ID " +
                "                     AND ROWNUM = 1) TYPE, " +
                "                (SELECT F.ID " +
                "                    FROM MAS_PRODUCT E, MAS_SERVE_TYPE F, MAS_SERVE_CATEGORY G " +
                "                   WHERE E.CLASSIFY_ID = G.CLASSIFY_ID " +
                "                     AND F.ID = G.SERVE_TYPE_ID " +
                "                     AND B.PRODUCT_ID = E.ID " +
                "                     AND ROWNUM = 1) SERVE_TYPE_ID, " +
                "                (B.PRICE_TOTAL - B.DISCOUNT) PRICE_TOTAL, " +
                "                (SELECT SUM(E.TAX_COST_PRICE * E.PRODUCT_QTY) " +
                "                    FROM ACC_VOUCHER_INVENTORY E " +
                "                   WHERE E.VOUCHER_ITEM_ID = B.ID) COST " +
                "           FROM ACC_VOUCHER A, ACC_VOUCHER_ITEM B " +
                "          WHERE A.ID = B.VOUCHER_ID " +
                "            AND A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "            AND A.SCENE_ID = 'HC000' " +
                "            AND A.ORIGIN_BUSINESS_TYPE = 'ConsumableUse' " +
                "    ) C " +
                " WHERE 1 = 1 ";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /****
     * 会员联盟结算
     */
    public JSONArray queryCrossCompany(JSONObject param) {
        String sSQL = "SELECT C.*,to_char(CREATE_DATE,'yyyy-MM-dd') STATISTIC_DATE," +
                " (select '['||d.id||']'||d.name from mas_dept d where d.id = dept_id)dept  " +
                "  FROM (select a.STATISTIC_DATE CREATE_DATE, " +
                "             e.NAME                                                              TYPE, " +
                "             e.id                                                                SERVE_TYPE_ID, " +
                "             a.DEPT_ID, " +
                "             a.COMPANY_ID, " +
                "             DECODE(a.TYPE,'in','收入','out','支出',a.TYPE) TYPE_NAME, " +
                "             B.PRODUCT_ID," +
                "             a.PARTNER_COMPANY_ID, " +
                "             a.PARTNER_DEPT_ID, " +
                "             (case when VIP_BALANCE_TYPE = '3' then b.SETTLEMENT_AMOUNT else 0 end) hb, " +
                "             (case when VIP_BALANCE_TYPE = '1' then b.SETTLEMENT_AMOUNT else 0 end) bb, " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.SETTLEMENT_AMOUNT else 0 end) kq_yj, " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.AMOUNT_USED else 0 end) kq_yh, " +
                "             (case when VIP_BALANCE_TYPE = '3' then b.SETTLEMENT_AMOUNT else 0 end) + " +
                "             (case when VIP_BALANCE_TYPE = '1' then b.SETTLEMENT_AMOUNT else 0 end) + " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.SETTLEMENT_AMOUNT else 0 end) - " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.AMOUNT_USED else 0 end) VIP_in, " +
                "             (case when VIP_BALANCE_TYPE = '3' then b.SETTLEMENT_AMOUNT else 0 end) + " +
                "             (case when VIP_BALANCE_TYPE = '1' then b.SETTLEMENT_AMOUNT else 0 end) + " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.SETTLEMENT_AMOUNT else 0 end) + " +
                "             (case when VIP_BALANCE_TYPE = '2' then b.AMOUNT_USED else 0 end) VIP_out " +
                "      from VIP_UNION_SETTLE a, " +
                "           VIP_UNION_SETTLE_item b " +
                "               left join MAS_PRODUCT c on b.PRODUCT_ID = c.ID " +
                "               left join MAS_SERVE_CATEGORY d on d.CLASSIFY_ID = c.CLASSIFY_ID and ROWNUM = 1 " +
                "               left join mas_serve_type e on e.ID = d.SERVE_TYPE_ID " +
                "      where a.id = b.VIP_UNION_SETTLE_ID " +
                "        and a.TYPE = :TYPE2 " +
                "    ) C " +
                " WHERE 1 = 1 ";
        return toJSONArray(find(contactSQL(sSQL, param), param));
    }

    /***
     * 获取需重新绘制的毛利日报
     */
    public JSONArray queryGrossProfitByRefresh(JSONObject param, String sSQL) {
        Query query = entityManager.createNativeQuery(sSQL);
        query.setParameter("P_DATE", DateUtil.format(param.getDate("P_DATE"),DateUtil.SHOW_DATE_FORMAT));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> resultList = query.getResultList();
        return toJSONArray(resultList);
    }


}
